const db = require("../db/index");
//导入 bcryptjs 密码加密包
const bcrypt = require("bcryptjs");
const crypto = require("crypto");
const fs = require("fs");
const XLSX = require("xlsx");
const path = require("path");

// 上传头像
exports.uploadAvatar = (req, res) => {
  const onlyId = crypto.randomUUID();
  let oldName = req.files[0].filename;
  let newName = Buffer.from(req.files[0].originalname, "latin1").toString(
    "utf8"
  );
  fs.renameSync("./public/upload/" + oldName, "./public/upload/" + newName);
  const sql = "insert into image set ?";
  db.query(
    sql,
    { image_url: `http://127.0.0.1:3000/upload/${newName}`, onlyId },
    (err, results) => {
      if (err) return res.cc(err);
      res.send({
        onlyId,
        status: 0,
        url: "http://127.0.0.1:3000/upload/" + newName,
      });
    }
  );
};
// 上传文件并解析为json
exports.uploadXLSX = (req, res) => {
  try {
    if (req.files) {
      let oldName = req.files[0].filename;
      let newName = Buffer.from(req.files[0].originalname, "latin1").toString(
        "utf8"
      );
      exports.tableNewName = newName;
      fs.renameSync("./public/upload/" + oldName, "./public/upload/" + newName);
      const filePath = path.join("./public/upload/", newName);

      // 读取文件内容
      fs.readFile(filePath, (err, data) => {
        // console.log(11);
        if (err) {
          console.error("读取文件时出错：", err);
          return;
        }
        // 将文件内容作为 Buffer 传递给 XLSX.read
        const workbook = XLSX.read(data, { type: "buffer" });
        // 假设只有一个工作表
        const sheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[sheetName];
        // 转换为JSON
        const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1 });

        // 创建表的表头名称
        let createTitleName = jsonData[0].join(",");
        exports.headerTitleName = createTitleName;
        // 创建表的每一行
        let createTabCol = jsonData[0]
          .map((columnName) => `${columnName} text`)
          .join(",\n\t\t");
        // 使用 map 方法遍历每个子数组，并将每个元素转换为字符串
        const convertedData = jsonData
          .slice(1)
          .map((subarray) => {
            return subarray.map((item) => String(item));
          })
          .filter((subarray) => {
            // 检查子数组是否包含非空字符串
            return subarray.some((item) => item.trim() !== "");
          });
        let tabId = 1;
        // 创建动态xls表
        const createTableSql = `CREATE TABLE IF NOT EXISTS my_table_${tabId} (
            id INT(11) NOT NULL AUTO_INCREMENT,
            ${createTabCol},
            PRIMARY KEY (id)
          )`;
        // 动态插入xls数据
        const insertTableSql = `INSERT INTO my_table_${tabId} (${createTitleName}) VALUES ?`;
        exports.thattabId = `my_table_${tabId}`;
        db.query(createTableSql, (err, result) => {
          console.log(err);
          if (err) return res.cc(err);
          console.log("表创建成功");
          db.query(insertTableSql, [convertedData], (err, results) => {
            console.log(err);
            if (err) return res.cc(err);
            console.log("插入成功");
            return res.send({
              status: 0,
              message: "录入成功",
              createTitleName,
            });
          });
        });
      });
    } else {
      return res.sendStatus(404);
    }
  } catch (error) {
    console.log(error.sqlMessage);
    res.send(error);
  }
};
// 获取导入学生表信息
exports.getStuTable = (req, res) => {
  try {
    let totalCount = 0;
    // 每页显示的数据条数;
    const pageSize = parseInt(req.body.pageSize);
    // //当前页码
    const pageNum = parseInt(req.body.currentPage);
    // //计算查询的偏移量
    const offset = (pageNum - 1) * pageSize;
    const sql1 = `select * from ${exports.thattabId} limit ${offset},${pageSize}`;
    const sqlCount = `SELECT COUNT(*) as totalCount FROM ${exports.thattabId}`;
    db.query(sql1, (err, result) => {
      if (err) return res.cc("查询错误！");
      db.query(sqlCount, (err, results) => {
        if (err) return res.cc("查询错误！");
        totalCount = results[0].totalCount;
        res.send({
          result,
          status: 200,
          message: "查询成功！",
          total: totalCount,
          headerTitleName: exports.headerTitleName,
        });
      });
    });
  } catch (error) {
    console.log(error);
  }
};
// 删除文件
exports.deleteFile = (req, res) => {
  try {
    const fsPromises = require("fs").promises;
    const filePath = path.join("./public/upload/", exports.tableNewName);
    fsPromises
      .unlink(filePath)
      .then(() => {
        console.log(`文件 ${filePath} 已被删除`);
      })
      .catch((err) => {
        console.error(`删除文件 ${filePath} 时出错:`, err);
      });
    const sql = `DROP TABLE ${exports.thattabId}`;
    db.query(sql, (err, results) => {
      if (err) return res.cc(err);
      res.send({
        status: 0,
        message: "删除成功",
      });
    });
  } catch (error) {
    console.log(error);
  }
};
// 对表格数据选择搜索信息
exports.searchTableInfo = (req, res) => {
  let totalCount = 0;
  // 每页显示的数据条数
  const pageSize = parseInt(req.body.pageSize) // 设置默认值
  // 当前页码
  const pageNum = parseInt(req.body.currentPage) // 设置默认值
  // 计算查询的偏移量
  const offset = (pageNum - 1) * pageSize;
  console.log(req.body);
  // 模糊查询的计数
  const sqlCount = `SELECT COUNT(*) as totalCount FROM ${exports.thattabId} WHERE ${req.body.selectName} LIKE ?`;
  db.query(sqlCount, `%${req.body.data}%`, (err, countResult) => {
    if (err) return res.cc(err);
    totalCount = countResult[0].totalCount;
    console.log(err);
    console.log(countResult);
    // 如果没有数据，直接返回
    if (totalCount === 0) {
      return res.send({
        status: 1,
        message: "没有查到任何数据！",
        total: 0,
      });
    }

    // 分页查询
    const sql1 = `SELECT * FROM ${exports.thattabId} WHERE ${req.body.selectName} LIKE ? LIMIT ?, ?`;
    db.query(sql1, [`%${req.body.data}%`, offset, pageSize], (err, results) => {
      console.log(err);
      if (err) return res.cc(err);
      // 发送响应
      res.send({
        results,
        status: 200,
        message: "查询成功！",
        total: totalCount,
      });
    });
  });
};
// 绑定账户
exports.bindAccount = (req, res) => {
  const { account, onlyId, url } = req.body;
  const sql = "update image set account = ? where onlyId = ?";
  db.query(sql, [account, onlyId], (err, results) => {
    if (err) return res.cc(err);
    if (results.affectedRows == 1) {
      const sql1 = "update users set image_url = ? where account = ?";
      db.query(sql1, [url, account], (err, results) => {
        if (err) return res.cc(err);
        res.send({
          status: 0,
          message: "修改成功",
        });
      });
    }
  });
};
// 获取用户信息 可传id,account
exports.getUserInfo = (req, res) => {
  if (req.body.id) {
    const sql = "select * from users where id = ?";
    db.query(sql, req.body.id, (err, results) => {
      if (err) return res.cc(err);
      results.forEach((val) => {
        val.password = "";
      });
      res.send(results);
    });
  } else if (req.body.account) {
    const sql = "select * from users where account = ?";
    db.query(sql, req.body.account, (err, results) => {
      if (err) return res.cc(err);
      results.forEach((val) => {
        val.password = "";
      });
      res.send(results);
    });
  } else {
    const sql1 = "select * from users";
    db.query(sql1, (err, results) => {
      if (err) return res.cc(err);
      results.forEach((val) => {
        val.password = "";
      });
      res.send(results);
    });

    //   每页显示的数据条数;
    //   const pageSize = parseInt(req.query.pageSize);
    //   //当前页码
    //   const pageNum = parseInt(req.query.currentPage);
    //   //计算查询的偏移量
    //   const offset = (pageNum - 1) * pageSize;
    //   const sql1 = `select * from ${exports.thattabId} limit ${offset},${pageSize}`;
    //   db.query(sql1, (err, results) => {
    //     if (err) return res.cc("查询错误！");
    //     res.send({
    //       results,
    //       status: 200,
    //       message: "查询成功！",
    //     });
    //   });
  }
};
// 修改信息，接受 id,name,sex,email
exports.changeInfo = (req, res) => {
  const { id, name, sex, email } = req.body;
  if (name) {
    const sql = "update users set name=? where id = ?";
    db.query(sql, [name, id], (err, results) => {
      if (err) return res.cc(err);
      res.send({
        status: 0,
        message: "姓名修改成功",
      });
    });
  } else if (sex) {
    const sql = "update users set sex=? where id = ?";
    db.query(sql, [sex, id], (err, results) => {
      if (err) return res.cc(err);
      res.send({
        status: 0,
        message: "性别修改成功",
      });
    });
  } else if (email) {
    const sql = "update users set email=? where id = ?";
    db.query(sql, [email, id], (err, results) => {
      if (err) return res.cc(err);
      res.send({
        status: 0,
        message: "邮箱修改成功",
      });
    });
  }
};
// 修改密码 根据id
exports.changePassword = (req, res) => {
  const { id, oldPassword, newPassword, twoPassword, email, account } =
    req.body;
  if (id) {
    const sql = "select password,email from users where id = ?";
    db.query(sql, id, (err, results) => {
      if (err) return res.cc(err);
      if (!results.length)
        return res.send({
          status: 1,
          message: "用户不存在",
        });
      const compareResult = bcrypt.compareSync(
        oldPassword,
        results[0].password
      );
      if (!compareResult)
        return res.send({
          status: 1,
          message: "原密码错误",
        });
      const hashedNewPassword = bcrypt.hashSync(newPassword, 10);
      const sql2 = "update users set password = ? where id = ?";
      db.query(sql2, [hashedNewPassword, id], (err, results) => {
        if (err) return res.cc(err);
        res.send({
          status: 0,
          message: "修改成功",
        });
      });
    });
  } else {
    const sql = "select * from users where account = ?";
    db.query(sql, account, (err, results) => {
      if (err) return res.cc(err);
      const compareResult = bcrypt.compareSync(
        oldPassword,
        results[0].password
      );
      if (!compareResult)
        return res.send({
          status: 1,
          message: "原密码错误",
        });
      if (newPassword !== twoPassword)
        return res.send({
          status: 1,
          message: "新密码与二次输入密码不一致",
        });
      const hashedNewPassword = bcrypt.hashSync(newPassword, 10);
      const sql2 = "update users set password = ? where account = ?";
      db.query(sql2, [hashedNewPassword, account], (err, results) => {
        if (err) return res.cc(err);
        res.send({
          status: 0,
          message: "修改成功",
        });
      });
    });
  }
};
// 查询邮箱和账号
exports.verifyAccountAndEmail = (req, res) => {
  const { account, email } = req.body;
  // 账号查询SQL
  const sqlAccount = "SELECT * FROM users WHERE account = ?";
  // 邮箱查询SQL
  const sqlEmail = "SELECT * FROM users WHERE email = ?";
  db.query(sqlAccount, account, (err, resultsAccount) => {
    if (err) return res.cc(err);
    db.query(sqlEmail, email, (err, resultsEmail) => {
      if (err) return res.cc(err);
      res.send({
        account: {
          status: resultsAccount.length > 0 ? 0 : 1,
          message:
            resultsAccount.length > 0
              ? "账号查询成功"
              : "账号查询失败，未找到匹配的账号",
        },
        email: {
          status: resultsEmail.length > 0 ? 0 : 1,
          message:
            resultsEmail.length > 0
              ? "邮箱查询成功"
              : "邮箱查询失败，未找到匹配的邮箱",
        },
      });
    });
  });
};
// 修改忘记密码
exports.changeForgetPwd = (req, res) => {
  req.body.newPassword = bcrypt.hashSync(req.body.newPassword, 10);
  const sql = "update users set password = ? where account = ?";
  db.query(sql, [req.body.newPassword, req.body.account], (err, results) => {
    if (err) return res.cc(err);
    res.send({
      status: 0,
      message: "修改成功",
    });
  });
};

// ---------------------------------------------------用户管理

// 添加管理员
exports.createAdmin = (req, res) => {
  const { account, newPassword, name, sex, department, email, identity } =
    req.body;
  const sql = "select * from users where account=? OR email=?";
  db.query(sql, [account, email], (err, results) => {
    if (err) return res.cc(err);
    if (results.length > 0) {
      return res.send({
        status: 1,
        message: "账号已存在",
      });
    } else {
      const hashpassword = bcrypt.hashSync(newPassword, 10);
      const sql1 = "insert into users set ?";
      const create_time = new Date();
      db.query(
        sql1,
        {
          account,
          password: hashpassword,
          name,
          sex,
          department,
          email,
          identity,
          create_time,
          status: 0,
        },
        (err, results) => {
          if (err) return res.cc(err);
          if (results.affectedRows !== 1)
            return res.cc("注册用户失败，请稍后再试！");
          res.cc("添加管理员成功！", 200);
        }
      );
    }
  });
};
// 获取产品管理员列表
exports.getAdminList = (req, res) => {
  const sql = "select * from users where identity=?";
  db.query(sql, req.body.identity, (err, results) => {
    if (err) return res.cc(err);
    results.forEach((val) => {
      (val.password = ""), (val.create_time = "");
    });
    res.send(results);
  });
};
// 获取部门筛选列表
exports.getDepartmentList = (req, res) => {
  if (req.body.department === undefined) req.body.department = "";
  const sql = "select * from users where department like ?";
  db.query(sql, `${req.body.department}%`, (err, results) => {
    if (err) return res.cc(err);
    results.forEach((val) => (val.password = ""));
    res.send(results);
  });
};
// 编辑管理员账户信息
exports.editorAdmin = (req, res) => {
  const { id, name, sex, email, department, identity } = req.body;
  const data = new Date();
  let updateContent = {
    id,
    name,
    sex,
    email,
    department,
    updata_time: data,
    identity,
  };
  const sql = "update users set ? where id=?";
  db.query(sql, [updateContent, updateContent.id], (err, results) => {
    if (err) return res.cc(err);
    res.send({
      status: 0,
      message: "修改管理员信息成功",
    });
  });
};
// 对管理员取消赋权
exports.changeIdentityToUser = (req, res) => {
  const identity = "用户";
  const sql = `update users set identity=? where id=?`;
  db.query(sql, [identity, req.body.id], (err, results) => {
    if (err) return res.cc(err);
    res.send({
      status: 0,
      message: "降级成功",
    });
  });
};
// 对管理员进行赋权
exports.changeIdentityToAdmin = (req, res) => {
  const sql = "update users set identity=? where id=?";
  db.query(sql, [req.body.identity, req.body.id], (err, results) => {
    if (err) return res.cc(err);
    res.send({
      status: 0,
      message: "赋权成功",
    });
  });
};
// 通过账号对用户搜索
exports.searchUser = (req, res) => {
  if (req.body.identity) {
    const sql = "SELECT * FROM users WHERE identity = ? AND account LIKE ?";
    db.query(
      sql,
      [req.body.identity, `${req.body.account}%`],
      (err, results) => {
        if (err) return res.cc(err);
        if (results.length === 0) {
          return res.send({
            status: 1,
            message: "没有查到任何数据！",
          });
        } else {
          results.forEach((val) => {
            (val.password = ""), (val.create_time = "");
          });
          res.send(results);
        }
      }
    );
  } else {
    const sql1 = `select * from users where account like ?`;
    db.query(sql1, `${req.body.account}%`, (err, results) => {
      if (err) return res.cc(err);
      if (results.length === 0) {
        return res.send({
          status: 1,
          message: "没有查到任何数据！",
        });
      } else {
        results.forEach((val) => (val.password = ""));
        res.send(results);
      }
    });
  }
};
// 冻结用户
exports.banUser = (req, res) => {
  const sql = "update users set status=? where id=?";
  db.query(sql, [1, req.body.id], (err, results) => {
    if (err) return res.cc(err);
    res.send({
      status: 0,
      message: "冻结成功",
    });
  });
};
// 解冻用户
exports.hotUser = (req, res) => {
  const sql = "update users set status=? where id=?";
  db.query(sql, [0, req.body.id], (err, results) => {
    if (err) return res.cc(err);
    res.send({
      status: 0,
      message: "解冻成功",
    });
  });
};
// 获取用户冻结列表
exports.getBanList = (req, res) => {
  const sql = "select * from users where status=1";
  db.query(sql, (err, results) => {
    if (err) return res.cc(err);
    res.send(results);
  });
};
// 删除用户
exports.deleteUser = (req, res) => {
  const sql = "delete from users where id=?";
  db.query(sql, req.body.id, (err, results) => {
    if (err) return res.cc(err);
    const sql1 = "delete from users where account=?";
    db.query(sql1, req.body.account, (err, results) => {
      if (err) return res.cc(err);
      res.send({
        status: 0,
        message: "删除成功",
      });
    });
  });
};
// 监听换页返回页码
exports.returnPageListData = (req, res) => {
  console.log(req.body, "--------");
  let totalCount = 0;
  // 每页显示的数据条数;
  const pageSize = parseInt(req.body.pageSize);
  // //当前页码
  const pageNum = parseInt(req.body.currentPage);
  // //计算查询的偏移量
  const offset = (pageNum - 1) * pageSize;
  const sql1 = `select * from my_table_1 limit ${offset},${pageSize}`;
  const sqlCount = "SELECT COUNT(*) as totalCount FROM my_table_1";
  db.query(sql1, (err, result) => {
    if (err) return res.cc("查询错误！");
    db.query(sqlCount, (err, results) => {
      if (err) return res.cc("查询错误！");
      totalCount = results[0].totalCount;
      res.send({
        result,
        status: 200,
        message: "查询成功！",
        total: totalCount,
      });
    });
  });

  // const number = req.body.pages;
  // const sql = "select * from users where identity=? limit 1 offset ?";
  // db.query(sql, [req.body.identity, `${number}`], (err, results) => {
  //   if (err) return res.cc(err);
  //   res.send(results);
  // });
};
